import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

public class ShowWin extends JFrame implements ActionListener{
    JButton OrderByNumber,OrderByGrade,exit;
    JTable table;
    JScrollPane scrollPane;
    Object[][] info;
    Object column_name[]={"学号","姓名","成绩","家乡"};
    OperationWin operationWin;
    String user;
    Connection con;
    Statement sql;
    ResultSet rs;
    int NumOfRow;
    ShowWin(String title,String user){
        try{
            con= DriverManager.getConnection("jdbc:sqlserver://localhost:49689;DatabaseName=Student","sa","061210");
            sql=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs=sql.executeQuery("SELECT * FROM student");
            rs.last();
            NumOfRow=rs.getRow();
            info=new Object[NumOfRow][4];
            rs.beforeFirst();
            int i=0;
            while(rs.next())
            {
                info[i][0]=rs.getString(1);
                info[i][1]=rs.getString(2);
                info[i][2]=rs.getString(3);
                info[i][3]=rs.getString(4);
                i++;
            }
            con.close();
        }
        catch(SQLException e){
            JOptionPane.showMessageDialog(this,e,"WARNING",JOptionPane.WARNING_MESSAGE);
            System.exit(0);
        }
        setTitle(title);
        this.user=user;
        table=new JTable(info,column_name);
        OrderByNumber=new JButton("按学号排序");
        OrderByNumber.addActionListener(this);
        OrderByGrade=new JButton("按成绩排序");
        OrderByGrade.addActionListener(this);
        exit=new JButton("退出");
        exit.addActionListener(this);
        scrollPane=new JScrollPane(table);

        setLayout(null);
        add(OrderByGrade);
        add(OrderByNumber);
        add(scrollPane);
        add(exit);
        OrderByGrade.setBounds(80,150,100,40);
        OrderByNumber.setBounds(80,250,100,40);
        exit.setBounds(80,350,100,40);
        scrollPane.setBounds(300,50,400,450);

        setBounds(600,300,800,600);
        setVisible(true);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }
    public void actionPerformed(ActionEvent event){
        if(event.getSource()==exit){
            if (JOptionPane.showConfirmDialog(this, "是否退出", "提示", JOptionPane.YES_NO_CANCEL_OPTION) == JOptionPane.YES_OPTION) {
                dispose();
                operationWin=new OperationWin("系统操作界面",user);
            }
        }
        else if(event.getSource()==OrderByNumber){
            try{
                con= DriverManager.getConnection("jdbc:sqlserver://localhost:49689;DatabaseName=Student","sa","061210");
                sql=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                rs=sql.executeQuery("SELECT * FROM student ORDER BY Number");
                int i=0;
                while(rs.next())
                {
                    info[i][0]=rs.getString(1);
                    info[i][1]=rs.getString(2);
                    info[i][2]=rs.getString(3);
                    info[i][3]=rs.getString(4);
                    i++;
                }
                table.repaint();
                con.close();
            }
            catch(SQLException e){
                JOptionPane.showMessageDialog(this,e,"WARNING",JOptionPane.WARNING_MESSAGE);
                System.exit(0);
            }
        }
        else if(event.getSource()==OrderByGrade){
            try{
                con= DriverManager.getConnection("jdbc:sqlserver://localhost:49689;DatabaseName=Student","sa","061210");
                sql=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                rs=sql.executeQuery("SELECT * FROM student ORDER BY Grade");
                int i=0;
                while(rs.next())
                {
                    info[i][0]=rs.getString(1);
                    info[i][1]=rs.getString(2);
                    info[i][2]=rs.getString(3);
                    info[i][3]=rs.getString(4);
                    i++;
                }
                table.repaint();
                con.close();
            }
            catch(SQLException e){
                JOptionPane.showMessageDialog(this,e,"WARNING",JOptionPane.WARNING_MESSAGE);
                System.exit(0);
            }
        }
    }
}
